﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Microsoft.ApplicationBlocks.Data;
using System.Configuration;
using BC;
using BE;
using COMExcel = Microsoft.Office.Interop.Excel;
using System.IO;
using System.Threading;
namespace ModuleDuoc.Report
{
    public partial class frmTK_XuatNhap : ModuleDuoc.Base.frmTK_Base
    {
        DataTable _dt = new DataTable();
        public frmTK_XuatNhap()
        {
            InitializeComponent();
        }
        private void loadLoaiThuoc()
        {
            string sql = "select MANHOM,TENNHOM from zD_DM_VATTU_NHOMVATTU union select '',N'--Tất cả--'";
            DataTable dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                this.cboTenNhom.DataSource = dt;
                this.cboTenNhom.ValueMember = "MANHOM";
                this.cboTenNhom.DisplayMember = "TENNHOM";
                this.cboTenNhom.SelectedValue = "";
            }
        }
        private void loadThuoc(string where)
        {
            string sql = "select MAVT,TENVT from zD_DM_THUOC " + where +" union select '',N'--Tất cả--' ";
            DataTable dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql).Tables[0];
            if (dt.Rows.Count > 0)
            {
                this.cboTenThuoc.DataSource = dt;
                this.cboTenThuoc.ValueMember = "MAVT";
                this.cboTenThuoc.DisplayMember = "TENVT";
            }
        }
        private void loadLoaiHang()
        {
            List<BE.ZD_DM_VATTU_NHOMVATTU> lst = new List<ZD_DM_VATTU_NHOMVATTU>();
            BC.ZD_DM_VATTU_NHOMVATTUBC f = new ZD_DM_VATTU_NHOMVATTUBC();
            lst = f.GetAll_List();
            this.cboTenNhom.DataSource = lst;
            this.cboTenNhom.ValueMember = "MANHOM";
            this.cboTenNhom.DisplayMember = "TENNHOM";
        }

        private void frmTK_XuatNhap_Load(object sender, EventArgs e)
        {
            this.dgvData.AutoGenerateColumns = false;
            loadLoaiThuoc();
        }

        /// <summary>
        /// Xem kết quả
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        public override void btnView_Click(object sender, EventArgs e)
        {
            base.btnView_Click(sender, e);
            string sql = "zD_BC_TONKHO";

            System.Data.SqlClient.SqlParameter tungay = new System.Data.SqlClient.SqlParameter();
            tungay.ParameterName = "@tungay";
            tungay.DbType = DbType.DateTime;
            tungay.Direction = ParameterDirection.Input;
            tungay.Value = dtpFrom.Value;

            System.Data.SqlClient.SqlParameter denngay = new System.Data.SqlClient.SqlParameter();
            denngay.ParameterName = "@denngay";
            denngay.DbType = DbType.DateTime;
            denngay.Direction = ParameterDirection.Input;
            denngay.Value = dtpTo.Value;
            System.Data.SqlClient.SqlParameter mavt;

            if (cboTenThuoc.SelectedValue.ToString() == "")
            {
                mavt = new System.Data.SqlClient.SqlParameter("@mavt", "");
            }
            else
            {
                mavt = new System.Data.SqlClient.SqlParameter("@mavt", cboTenThuoc.SelectedValue.ToString());
            }
            System.Data.SqlClient.SqlParameter manhom;
            if (cboTenNhom.SelectedValue.ToString() == "")
            {
                manhom = new System.Data.SqlClient.SqlParameter("@manhom", "");
            }
            else
            {
                manhom = new System.Data.SqlClient.SqlParameter("@manhom", cboTenNhom.SelectedValue.ToString());
            }
            System.Data.SqlClient.SqlParameter nhacc = new System.Data.SqlClient.SqlParameter("@nhacc", "");
            System.Data.SqlClient.SqlParameter wherein = new System.Data.SqlClient.SqlParameter("@wherein", "");

            string sql_select = "select tk.MAVT,tk.tenvt,tk.dvt,tk.tennhom,sum(tk.tondauky) as tondauky,sum(tk.tiendauky) as tiendauky, sum(tk.slnhap) as slnhap, sum(tk.tiennhap) as tiennhap,";
            sql_select += " sum(tk.slxuat) as slxuat,sum(tk.tienxuat) as tienxuat,sum(tk.toncuoi) as toncuoi,sum(tk.tiencuoi) as tiencuoi,AVG(tk.dongianhap) as dongianhap, AVG(tk.dongiaxuat) as dongiaxuat,sum(tonthucte) as tonthucte";
            sql_select += " from zD_bcTonkhoth tk";
            sql_select += " where tondauky>0 or slnhap>0 or slxuat>0 or toncuoi>0";
            sql_select += " group by tk.MAVT,tk.tenvt,tk.dvt,tk.tennhom";

            SqlHelper.ExecuteNonQuery(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.StoredProcedure, sql,
                tungay,
                denngay,
                mavt,
                manhom,
                nhacc,
                wherein);

            DataTable dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql_select).Tables[0];
            this.dgvData.DataSource = dt;
            _dt = dt;

            int bangthucte = 0;
            int khacthucte = 0;
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dgvData.Rows.Count; i++)
                {
                    if (int.Parse(dgvData.Rows[i].Cells["toncuoi"].Value.ToString()) == int.Parse(dgvData.Rows[i].Cells["tonthucte"].Value.ToString()))
                    {
                        bangthucte += 1;
                    }
                    else
                    {
                        khacthucte += 1;
                        dgvData.Rows[i].DefaultCellStyle.BackColor = Color.Red;
                    }
                }
            }
            this.lblBangTonThucTe.Text = bangthucte.ToString();
            lblKhongBangTonThucTe.Text = khacthucte.ToString();
        }

        private void cboTenNhom_SelectedIndexChanged(object sender, EventArgs e)
        {
            this.loadThuoc("");
        }

        /// <summary>
        /// Xuất Excel
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        public override void btnExcel_Click(object sender, EventArgs e)
        {
            xuatExcel();
        }
        private void xuatExcel()
        {
            try
            {
                double progress = 0;
                this.progressBar1.Maximum = dgvData.Rows.Count;

                COMExcel.Application exApp = new COMExcel.Application();
                // Thêm file temp xls
                COMExcel.Workbook exBook = exApp.Workbooks.Add(
                          COMExcel.XlWBATemplate.xlWBATWorksheet);
                // Lấy sheet 1.
                COMExcel.Worksheet exSheet = (COMExcel.Worksheet)exBook.Worksheets[1];
                exSheet.Name = "TỔNG HỢP KIỂM KÊ THUỐC";

                COMExcel.Range ID = (COMExcel.Range)exSheet.get_Range("A1", "A1");
                ID.Value2 = "Mavt";
                ID.EntireRow.Font.Name = "Arial";
                ID.EntireRow.Font.Size = 10;
                ID.EntireRow.Font.Bold = true;

                COMExcel.Range MACHI = (COMExcel.Range)exSheet.get_Range("B1", "B1");
                MACHI.Value2 = "Tên thuốc";
                MACHI.EntireRow.Font.Name = "Arial";
                MACHI.EntireRow.Font.Size = 10;
                MACHI.EntireRow.Font.Bold = true;

                COMExcel.Range NGUOIDUYET = (COMExcel.Range)exSheet.get_Range("C1", "C1");
                NGUOIDUYET.Value2 = "ĐVT";
                NGUOIDUYET.EntireRow.Font.Name = "Arial";
                NGUOIDUYET.EntireRow.Font.Size = 10;
                NGUOIDUYET.EntireRow.Font.Bold = true;


                COMExcel.Range NGAYDUYET = (COMExcel.Range)exSheet.get_Range("D1", "D1");
                NGAYDUYET.Value2 = "Nhóm thuốc";
                NGAYDUYET.EntireRow.Font.Name = "Arial";
                NGAYDUYET.EntireRow.Font.Size = 10;
                NGAYDUYET.EntireRow.Font.Bold = true;

                COMExcel.Range TONGTIEN = (COMExcel.Range)exSheet.get_Range("E1", "E1");
                TONGTIEN.Value2 = "Tồn đầu kỳ";
                TONGTIEN.EntireRow.Font.Name = "Arial";
                TONGTIEN.EntireRow.Font.Size = 10;
                TONGTIEN.EntireRow.Font.Bold = true;


                COMExcel.Range TIENCHI = (COMExcel.Range)exSheet.get_Range("F1", "F1");
                TIENCHI.Value2 = "Tiền đầu kỳ";
                TIENCHI.EntireRow.Font.Name = "Arial";
                TIENCHI.EntireRow.Font.Size = 10;
                TIENCHI.EntireRow.Font.Bold = true;

                COMExcel.Range CONNO = (COMExcel.Range)exSheet.get_Range("G1", "G1");
                CONNO.Value2 = "Số lượng nhập";
                CONNO.EntireRow.Font.Name = "Arial";
                CONNO.EntireRow.Font.Size = 10;
                CONNO.EntireRow.Font.Bold = true;

                COMExcel.Range SOPHIEU = (COMExcel.Range)exSheet.get_Range("H1", "H1");
                SOPHIEU.Value2 = "Tiền nhập";
                SOPHIEU.EntireRow.Font.Name = "Arial";
                SOPHIEU.EntireRow.Font.Size = 10;
                SOPHIEU.EntireRow.Font.Bold = true;

                COMExcel.Range SL_XUAT = (COMExcel.Range)exSheet.get_Range("I1", "I1");
                SL_XUAT.Value2 = "Số lượng xuất";
                SL_XUAT.EntireRow.Font.Name = "Arial";
                SL_XUAT.EntireRow.Font.Size = 10;
                SL_XUAT.EntireRow.Font.Bold = true;

                COMExcel.Range TIEN_XUAT = (COMExcel.Range)exSheet.get_Range("J1", "J1");
                TIEN_XUAT.Value2 = "Tiền xuất";
                TIEN_XUAT.EntireRow.Font.Name = "Arial";
                TIEN_XUAT.EntireRow.Font.Size = 10;
                TIEN_XUAT.EntireRow.Font.Bold = true;


                COMExcel.Range TONCUOI = (COMExcel.Range)exSheet.get_Range("K1", "K1");
                TONCUOI.Value2 = "Tồn cuối kỳ";
                TONCUOI.EntireRow.Font.Name = "Arial";
                TONCUOI.EntireRow.Font.Size = 10;
                TONCUOI.EntireRow.Font.Bold = true;

                COMExcel.Range TIENCUOI = (COMExcel.Range)exSheet.get_Range("L1", "L1");
                TIENCUOI.Value2 = "Tiền cuối kỳ";
                TIENCUOI.EntireRow.Font.Name = "Arial";
                TIENCUOI.EntireRow.Font.Size = 10;
                TIENCUOI.EntireRow.Font.Bold = true;

                int start = 2;
                for (int i = 0; i < dgvData.Rows.Count - 1; i++)
                {
                    if (dgvData.Rows[i].Visible == true)
                    {
                        progress = i + 1;
                        // hien thi chay % tren thanh process, lam tron 2 ki tu
                        lblText.Text = Math.Round(((progress / dgvData.Rows.Count) * 100), 2).ToString() + "%";
                        progressBar1.Value = i + 1;
                        Thread.Sleep(2);
                        Application.DoEvents();
                        //Ma vt
                        COMExcel.Range ID_V = (COMExcel.Range)exSheet.get_Range("A" + (i + start), "A" + (i + start));
                        ID_V.Value2 = dgvData["mavt", i].Value.ToString();
                        ID_V.EntireColumn.AutoFit();

                        //Ma vt
                        COMExcel.Range MACHI_V = (COMExcel.Range)exSheet.get_Range("B" + (i + start), "B" + (i + start));
                        MACHI_V.Value2 = dgvData["tenvt", i].Value.ToString();
                        MACHI_V.EntireColumn.AutoFit();


                        //Ma vt
                        COMExcel.Range MANHANVIEN_V = (COMExcel.Range)exSheet.get_Range("C" + (i + start), "C" + (i + start));
                        MANHANVIEN_V.Value2 = dgvData["dvt", i].Value.ToString();
                        MANHANVIEN_V.EntireColumn.AutoFit();

                        //Ma vt
                        COMExcel.Range NGAYDUYET_V = (COMExcel.Range)exSheet.get_Range("D" + (i + start), "D" + (i + start));
                        NGAYDUYET_V.Value2 = dgvData["tennhom", i].Value.ToString();
                        NGAYDUYET_V.EntireColumn.AutoFit();

                        //Ma vt
                        COMExcel.Range TONGTIEN_V = (COMExcel.Range)exSheet.get_Range("E" + (i + start), "E" + (i + start));
                        TONGTIEN_V.Value2 = dgvData["tondauky", i].Value.ToString();
                        TONGTIEN_V.EntireColumn.AutoFit();

                        //Ma vt
                        COMExcel.Range TIENCHI_V = (COMExcel.Range)exSheet.get_Range("F" + (i + start), "F" + (i + start));
                        TIENCHI_V.Value2 = dgvData["tiendauky", i].Value.ToString();
                        TIENCHI_V.EntireColumn.AutoFit();

                        //Ma vt
                        COMExcel.Range CONNO_V = (COMExcel.Range)exSheet.get_Range("G" + (i + start), "G" + (i + start));
                        CONNO_V.Value2 = dgvData["slnhap", i].Value.ToString();
                        CONNO_V.EntireColumn.AutoFit();

                        //Ma vt
                        COMExcel.Range SOPHIEU_V = (COMExcel.Range)exSheet.get_Range("H" + (i + start), "H" + (i + start));
                        SOPHIEU_V.Value2 = dgvData["tiennhap", i].Value.ToString();
                        SOPHIEU_V.EntireColumn.AutoFit();

                        COMExcel.Range SLXUAT = (COMExcel.Range)exSheet.get_Range("I" + (i + start), "I" + (i + start));
                        SLXUAT.Value2 = dgvData["slxuat", i].Value.ToString();
                        SLXUAT.EntireColumn.AutoFit();

                        COMExcel.Range TIENXUAT = (COMExcel.Range)exSheet.get_Range("J" + (i + start), "J" + (i + start));
                        SLXUAT.Value2 = dgvData["tienxuat", i].Value.ToString();
                        SLXUAT.EntireColumn.AutoFit();

                        COMExcel.Range TONCUOI_HEAD = (COMExcel.Range)exSheet.get_Range("K" + (i + start), "K" + (i + start));
                        TONCUOI_HEAD.Value2 = dgvData["toncuoi", i].Value.ToString();
                        TONCUOI_HEAD.EntireColumn.AutoFit();

                        COMExcel.Range TIENCUOI_HEAD = (COMExcel.Range)exSheet.get_Range("L" + (i + start), "L" + (i + start));
                        TIENCUOI_HEAD.Value2 = dgvData["tiencuoi", i].Value.ToString();
                        TIENCUOI_HEAD.EntireColumn.AutoFit();
                    }
                }

                // Ẩn chương trình
                exApp.Visible = false;

                // Save file
                exBook.SaveAs("D:\\DS_DUYETCHI.xls", COMExcel.XlFileFormat.xlWorkbookNormal,
                                null, null, false, false,
                                COMExcel.XlSaveAsAccessMode.xlExclusive,
                                false, false, false, false, false);
                exApp.Quit();
                MessageBox.Show("Lưu thành công tại : D:\\XNTON_DUOC.xls", "Thông báo");

                FileInfo fi = new FileInfo("D:\\XNTON_DUOC.xls");
                if (fi.Exists)
                {
                    System.Diagnostics.Process.Start(@"D:\\XNTON_DUOC.xls");
                }
                else
                {
                    //
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message, "Lỗi", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

        }

        private void btnXuatNguyenMau_Click(object sender, EventArgs e)
        {
            DataTable dt = new DataTable();
            dt = _dt;
            System.Windows.Forms.SaveFileDialog saveFileDialog1 = new System.Windows.Forms.SaveFileDialog();
            saveFileDialog1.InitialDirectory = "D:\\";
            saveFileDialog1.Title = "Save Files";
            saveFileDialog1.CheckFileExists = false;
            saveFileDialog1.CheckPathExists = true;
            saveFileDialog1.DefaultExt = "xls";
            saveFileDialog1.Filter = "Excel 97-2003 files (*.xls)|*.xls|Excel files (*.xlsx)|*.xlsx|All files (*.*)|*.*";
            saveFileDialog1.FilterIndex = 1;

            saveFileDialog1.RestoreDirectory = true;

            string fileName = "";
            if (saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
            {
                fileName = saveFileDialog1.FileName;
                System.Web.UI.WebControls.DataGrid grid = new System.Web.UI.WebControls.DataGrid();
                grid.HeaderStyle.Font.Bold = true;
                grid.DataSource = dt;
                grid.DataMember = dt.TableName;
                grid.DataBind();
                using (System.IO.StreamWriter sw = new System.IO.StreamWriter(fileName, true, System.Text.Encoding.Unicode))
                {
                    using (System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter(sw))
                    {
                        grid.RenderControl(hw);
                    }
                }
                MessageBox.Show("Dữ liệu đã được xuất tại : " + fileName, "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
        }

        public override void btnExportReport_Click(object sender, EventArgs e)
        {
            base.btnExportReport_Click(sender, e);
            string sql = "SELECT mahanghoa,mavt,tenvt,dvt,manhom,tennhom,sum(tondauky) as tondauky,sum(tiendauky) as tiendauky,";
            sql += "sum(slnhap) as slnhap,sum(tiennhap) as tiennhap,sum(slxuat) as slxuat,";
            sql += "sum(tienxuat) as tienxuat,sum(toncuoi)as toncuoi,sum(toncuoi*dongianhap) as tiencuoi,avg(dongianhap) as dongianhap,avg(dongiaxuat) as dongiaxuat";
            sql += " from zD_bcTonkhoth WHERE mavt >0 ";
            sql += " and tondauky>0 or slnhap>0 or slxuat>0 or toncuoi>0";
            sql += " GROUP BY mahanghoa,mavt,tenvt,dvt,manhom,tennhom";
            sql += " ORDER BY tenvt,mavt";
            DataTable dt = SqlHelper.ExecuteDataset(
                ConfigurationManager.AppSettings["Main.ConnectionString"],
                CommandType.Text,
                sql).Tables[0];
            frmShowReport frm = new frmShowReport(1);
            frmShowReport.strC = frmShowReport.BC.TONGHOPXNTON;
            frmShowReport._dt = dt;
            frmShowReport._date1 = dtpFrom.Value.ToString("dd/MM/yyyy");
            frmShowReport._date2 = dtpTo.Value.ToString("dd/MM/yyyy");
            frm.Show();
        }

        private void dgvData_Paint(object sender, PaintEventArgs e)
        {
            //string[] monthes = { "","","Tồn đầu ký", "Nhập trong kỳ", "Xuất trong kỳ", "Tồn cuối kỳ", "" };
            //for (int j = 0; j < 10; )
            //{
            //    Rectangle r1 = this.dgvData.GetCellDisplayRectangle(j, -1, true);
            //    int w2 = this.dgvData.GetCellDisplayRectangle(j + 1, -1, true).Width;
            //    r1.X += 1;
            //    r1.Y += 1;
            //    r1.Width = r1.Width + w2 - 2;
            //    r1.Height = r1.Height / 2 - 2;
            //    e.Graphics.FillRectangle(new SolidBrush(this.dgvData.ColumnHeadersDefaultCellStyle.BackColor), r1);
            //    StringFormat format = new StringFormat();
            //    format.Alignment = StringAlignment.Center;
            //    format.LineAlignment = StringAlignment.Center;
            //    e.Graphics.DrawString(monthes[j / 2],
            //    this.dgvData.ColumnHeadersDefaultCellStyle.Font,
            //    new SolidBrush(this.dgvData.ColumnHeadersDefaultCellStyle.ForeColor),
            //    r1,
            //    format);
            //    j += 2;
            //}
        }

        private void dgvData_CellPainting(object sender, DataGridViewCellPaintingEventArgs e)
        {
            //if (e.RowIndex == -1 && e.ColumnIndex > -1)
            //{
            //    Rectangle r2 = e.CellBounds;
            //    r2.Y += e.CellBounds.Height / 2;
            //    r2.Height = e.CellBounds.Height / 2;
            //    e.PaintBackground(r2, true);
            //    e.PaintContent(r2);
            //    e.Handled = true;
            //}
        }

        private void btnFilter_Click(object sender, EventArgs e)
        {
            string sql_select = "select tk.MAVT,tk.tenvt,tk.dvt,tk.tennhom,sum(tk.tondauky) as tondauky,sum(tk.tiendauky) as tiendauky, sum(tk.slnhap) as slnhap, sum(tk.tiennhap) as tiennhap,";
            sql_select += " sum(tk.slxuat) as slxuat,sum(tk.tienxuat) as tienxuat,sum(tk.toncuoi) as toncuoi,sum(tk.tiencuoi) as tiencuoi,AVG(tk.dongianhap) as dongianhap, AVG(tk.dongiaxuat) as dongiaxuat,sum(tonthucte) as tonthucte";
            sql_select += " from zD_bcTonkhoth tk";
            sql_select += " where tonthucte>0";
            sql_select += " group by tk.MAVT,tk.tenvt,tk.dvt,tk.tennhom";

            DataTable dt = SqlHelper.ExecuteDataset(ConfigurationManager.AppSettings["Main.ConnectionString"], CommandType.Text, sql_select).Tables[0];
            this.dgvData.DataSource = dt;

            int bangthucte = 0;
            int khacthucte = 0;

            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dgvData.Rows.Count; i++)
                {
                    if (int.Parse(dgvData.Rows[i].Cells["toncuoi"].Value.ToString()) == int.Parse(dgvData.Rows[i].Cells["tonthucte"].Value.ToString()))
                    {
                        bangthucte += 1;
                    }
                    else
                    {
                        khacthucte += 1;
                        dgvData.Rows[i].DefaultCellStyle.BackColor = Color.Red;
                    }
                }
            }
            this.lblBangTonThucTe.Text = bangthucte.ToString();
            lblKhongBangTonThucTe.Text = khacthucte.ToString();
        }
    }
}
